package khfx.dao.common.impl;

import java.util.ArrayList;
import java.util.List;

import khfx.dao.common.SelectGroupBalanceDao;
import khfx.dao.db.SQLQuery;
import khfx.dao.db.Session;
import khfx.domain.common.GroupBalanceSelectVo;

import org.springframework.stereotype.Component;

@Component("SelectGroupBalanceDao")
public class SelectGroupBalanceDaoImpl implements SelectGroupBalanceDao{

	@SuppressWarnings("unchecked")
	public List<GroupBalanceSelectVo> selectGroupBalance (Session session,Integer reportDate){
		List<GroupBalanceSelectVo> list = new ArrayList<GroupBalanceSelectVo>();
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT G.GROUPID, ");
		sql.append("SUM(C.CREDITLIMIT) AS CREDITLIMIT, ");
		sql.append("SUM(C.LOANBALANCE) AS LOANBALANCE, ");
		sql.append("SUM(C.BONDBALANCE) AS BONDBALANCE, ");
		sql.append("SUM(C.EQUITYSTAKEBALANCE) AS EQUITYSTAKEBALANCE, ");
		sql.append("SUM(C.OFFBALANCESHEET) AS OFFBALANCESHEET ");
		sql.append("FROM ");
		sql.append("(SELECT * FROM C_GROUP WHERE REPORTDATE= ? ) G ");
		sql.append("INNER JOIN ");
		sql.append("(SELECT * from C_CREDIT  WHERE REPORTDATE= ? ) C ");
		sql.append("ON G.BANKCODE = C.BANKCODE AND G.CUSTOMERCODE = C.CUSTOMERCODE ");
		sql.append("GROUP BY  G.GROUPID WITH UR ");
		String sqlStr = sql.toString();
		SQLQuery query = session.createSQLQuery(sqlStr);
		query.setParameter(1, reportDate);
		query.setParameter(2, reportDate); 
		query.addEntity(GroupBalanceSelectVo.class);
		list = (List<GroupBalanceSelectVo>) query.executeQuery();
		return list;
	}
}
